﻿using System.Text;
using BaseData.CommonLib.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace BaseData.ApplicationLib.Queries;

public class WarehouseQueries : IWarehouseQueries
{
    private readonly string? _defaultConnStr;

    public WarehouseQueries(string? defaultConnStr)
    {
        _defaultConnStr = defaultConnStr;
    }

    public async Task<PaginatedResult<WarehouseInfoResponseDto>> QueryWarehousesByPage(WarehouseSearchRequestDto model)
    {
        
        IEnumerable<WarehouseInfoResponseDto>? data;
        int totalCount;
        using (var db = new SqlConnection(_defaultConnStr))
        {
            var query = new Query(db);

            StringBuilder sqlBuilder = new StringBuilder(@"
select id,
       code,
       name,
       warehousetype,
       description,
       address,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_WAREHOUSE t where 1 = 1 ");

            query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

            data = await db.QueryAsync<WarehouseInfoResponseDto>(query.SqlPage);

            totalCount = await db.ExecuteScalarAsync<int>(query.SqCount);
        }

        return await PaginatedResult<WarehouseInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
            model.Page,
            model.Limit);
    }

    public async Task<Result<IList<WarehouseInfoResponseDto>>?> QueryWarehouses(WarehouseSearchRequestDto model)
    {
        IEnumerable<WarehouseInfoResponseDto>? data;
        using (var db = new SqlConnection(_defaultConnStr))
        {
            data = await db.QueryAsync<WarehouseInfoResponseDto>(@"
select id,
       code,
       name,
       warehousetype,
       description,
       address,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_WAREHOUSE t where 1 = 1 ");
        }

        return await Result<IList<WarehouseInfoResponseDto>>.SuccessAsync(data.ToList());
    }

    public async Task<Result<WarehouseInfoResponseDto>?> GetWarehouse(int id)
    {
        WarehouseInfoResponseDto? data;

        using (var db = new SqlConnection(_defaultConnStr))
        {
            data = await db.QueryFirstOrDefaultAsync<WarehouseInfoResponseDto>(@"
select id,
       code,
       name,
       warehousetype,
       description,
       address,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_WAREHOUSE t where 1 = 1 and id = @id ", new { id });
        }

        return await Result<WarehouseInfoResponseDto>.SuccessAsync(data);
    }
}